package com.li.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.li.client.entities.RoleAdmin;
import com.li.client.entities.User;
import com.li.client.entities.UserRole;
import com.li.server.jdbc.JDBCExecutor;
import com.li.server.jdbc.JDBCUtil;

public class UserDaoImpl implements UserDao {

	@Override
	public User getUserByUserId(final String userId) {
		return new JDBCUtil().executeSQL(new JDBCExecutor<User>() {

			@Override
			public User execute(Connection conn) throws Exception {
				PreparedStatement queryUserStmt = conn.prepareStatement("SELECT PK_USER_ID, LOGIN_ID, PASSWORD, FIRST_NAME, LAST_NAME, UR.PK_ROLE_ID, UR.ROLE_NAME " +
						"FROM USER, USER_ROLE UR " +
						"WHERE LOGIN_ID = ? " +
						" and FK_USER_ROLE_ID = PK_ROLE_ID");
				queryUserStmt.setString(1, userId);
				
				ResultSet rs = queryUserStmt.executeQuery();
				User u = null;
				UserRole ur = new UserRole();
				
				while(rs.next()) {
					u = new User();
					u.setPkUserId(rs.getInt("PK_USER_ID"));
					u.setLoginId(rs.getString("LOGIN_ID"));
					u.setFirstName(rs.getString("FIRST_NAME"));
					u.setLastName(rs.getString("LAST_NAME"));
					
					ur.setPkRoleId(rs.getInt("PK_ROLE_ID"));
					ur.setRoleName(rs.getString("ROLE_NAME"));
					
					u.setUserRole(ur);
				}
				
				if (u != null) {
					PreparedStatement stmt = conn.prepareStatement("SELECT FK_ROLE_ID, SECTION_NAME FROM ROLE_ADMIN WHERE FK_ROLE_ID = ? ");
					stmt.setInt(1, u.getPkUserId());
					
					ResultSet rsRole = stmt.executeQuery();
					List<RoleAdmin> raList = new ArrayList<RoleAdmin>();
					while(rsRole.next()) {
						RoleAdmin ra = new RoleAdmin();
						ra.setFkRoleId(rsRole.getInt("FK_ROLE_ID"));
						ra.setSectionName(rsRole.getString("SECTION_NAME"));
						
						raList.add(ra);
					}
					ur.setRoleAdmin(raList);
				}
				
				return u;
			}
		});
		
	}

	@Override
	public User loginSystem(final String loginId, final String pwd) {
		return new JDBCUtil().executeSQL(new JDBCExecutor<User>() {

			@Override
			public User execute(Connection conn) throws Exception {
				PreparedStatement queryUserStmt = conn.prepareStatement("SELECT PK_USER_ID" +
						", LOGIN_ID" +
						", PASSWORD" +
						", FIRST_NAME" +
						", LAST_NAME" +
						", UR.PK_ROLE_ID" +
						", UR.ROLE_NAME " +
						"FROM USER, USER_ROLE UR " +
						"WHERE LOGIN_ID = ? " +
						" AND PASSWORD = ? " +
						" AND FK_USER_ROLE_ID = PK_ROLE_ID");
				queryUserStmt.setString(1, loginId);
				queryUserStmt.setString(2, pwd);
				
				ResultSet rs = queryUserStmt.executeQuery();
				User u = null;
				UserRole ur = new UserRole();
				
				while(rs.next()) {
					u = new User();
					u.setPkUserId(rs.getInt("PK_USER_ID"));
					u.setLoginId(rs.getString("LOGIN_ID"));
					u.setFirstName(rs.getString("FIRST_NAME"));
					u.setLastName(rs.getString("LAST_NAME"));
					
					ur.setPkRoleId(rs.getInt("PK_ROLE_ID"));
					ur.setRoleName(rs.getString("ROLE_NAME"));
					
					u.setUserRole(ur);
				}
				
				if (u != null) {
					PreparedStatement stmt = conn.prepareStatement("SELECT FK_ROLE_ID, SECTION_NAME FROM ROLE_ADMIN WHERE FK_ROLE_ID = ? ");
					stmt.setInt(1, u.getPkUserId());
					
					ResultSet rsRole = stmt.executeQuery();
					List<RoleAdmin> raList = new ArrayList<RoleAdmin>();
					while(rsRole.next()) {
						RoleAdmin ra = new RoleAdmin();
						ra.setFkRoleId(rsRole.getInt("FK_ROLE_ID"));
						ra.setSectionName(rsRole.getString("SECTION_NAME"));
						
						raList.add(ra);
					}
					ur.setRoleAdmin(raList);
				}
				
				return u;
			}
		});
	}

}
